    package com.javaweb.dao.impl;

    import com.javaweb.aop.TimeMeasure;
    import com.javaweb.common.StringUtils;
    import com.javaweb.dao.UserDao;
    import com.javaweb.model.User;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;

    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;

    /**
     * @desc:
     * @author: zxzx
     * @date: 2024/9/23 16:10
     */
    @Repository
    @Slf4j
    public class UserDaoImpl implements UserDao {

        @Autowired
        private Connection conn ;
        @Autowired
        private JdbcTemplate jdbcTemplate ;
        @Override
        public Integer insertUser(User user) {

            /**
             * SQL 逻辑
             */
            String sql = "INSERT INTO tb_user (name, password, salt, create_time, gender) VALUES" +
                    "(?, ?, ?, ? ,?)" ;
            try (
                    PreparedStatement ps = this.conn.prepareStatement(sql)
            ) {
                ps.setString(1, user.getName()) ;
                ps.setString(2, StringUtils.md5Encoder(user.getPassword(),user.getSalt())) ;
                ps.setDate(4, new Date(user.getCreateTime().getTime()));
                ps.setString(3, user.getSalt()) ;
                ps.setInt(5, user.getGender()) ;
                return ps.executeUpdate() ;
            } catch (SQLException e) {
                log.error("execute insert user error: " + e.getMessage()) ;
            }
            return 0 ;
        }

        @Override
        public Integer insertUserRole(Integer userId, Integer roleId) {
            String sql = "INSERT INTO tb_user_role (user_id, role_id) VALUES (?,?)" ;
            try (
                    PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
                ps.setInt(1, userId) ;
                ps.setInt(2, roleId) ;
                return ps.executeUpdate() ;
            }catch (SQLException e) {
                log.error("execute insertUserRole error: " + e.getMessage()) ;
            }
            return 0;
        }

        @Override
        public User selectOne(String name, String password) {
            String sql = "SELECT * FROM tb_user WHERE name=? and password=?" ;
            try (PreparedStatement ps = this.conn.prepareStatement(sql) ;
            ) {
                ps.setString(1, name) ;
                ps.setString(2, StringUtils.md5Encoder(password, StringUtils.getSalt(name))) ;
                ResultSet rs = ps.executeQuery() ;
                if (rs.next()) {
                    User user = new User()
                            .setUserId(rs.getInt(1))
                            .setName(name)
                            .setPassword(password)
                            .setSalt(rs.getString(4))
                            .setCreateTime(rs.getDate(5))
                            .setGender(rs.getInt(6)) ;
                    return user ;
                }
            } catch (SQLException e) {
                log.error(e.getMessage()) ;
            }
            return null;
        }

        @Override
        public List<User> selectByName(String name) {

            return null;
        }

        @Override
        public List<User> selectByRole(String roleName) {
            String sql = "SELECT u.* FROM tb_role r " +
                                     " JOIN tb_user_role ur ON r.role_id = ur.role_id" +
                                     " JOIN tb_user u ON u.user_id = ur.user_id" +
                                    "  WHERE r.name = ?" ;
            List<User> userList = new ArrayList<>() ;
            try (PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
                ps.setString(1, roleName);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    User user = new User()
                            .setUserId(rs.getInt(1))
                            .setName(rs.getString(2))
                            .setPassword(rs.getString(3))
                            .setSalt(rs.getString(4))
                            .setCreateTime(rs.getDate(5))
                            .setGender(rs.getInt(6));
                    userList.add(user);
                }
                return userList;
            } catch (SQLException e) {
                log.error("execute selectByRole error: " + e.getMessage());
            }

            return userList;
        }

        @TimeMeasure
        @Override
        public Integer selectCountAll() {
            String sql = "SELECT COUNT(*) FROM tb_user" ;
            try (PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
                ResultSet rs = ps.executeQuery() ;
                if (rs.next()) {
                    return rs.getInt(1) ;
                }
            } catch (SQLException e) {
                log.error("selectCountAll error: " + e.getMessage()) ;
            }
            return 0;
        }

        @Override
        public Integer updateScore(Integer userId, int increased) {
            String sql = "UPDATE tb_user SET score = score + ? WHERE user_id = ?" ;
    //        try (
    //                PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
    //            ps.setInt(1, increased) ;
    //            ps.setInt(2, userId) ;
    //            return ps.executeUpdate() ;
    //        }catch (SQLException e) {
    //            log.error("execute update user score: " + e.getMessage()) ;
    //        }
            return jdbcTemplate.update(sql, new Object[]{increased, userId});
        }
        @Override
        public User findByName(String username) {
            String sql = "SELECT * FROM tb_user WHERE name = ?";
            return jdbcTemplate.queryForObject(sql, new Object[]{username}, (rs, rowNum) -> {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                user.setScore(rs.getInt("score"));
               user.setGender(rs.getInt("gender"));
                return user;
            });
        }
        // 插入用户


        // 根据条件查询用户列表
        public List<User> selectUsersByCondition(User userCondition, Integer pageNum, Integer pageSize) {
            String sql = "SELECT * FROM tb_user WHERE name LIKE ? LIMIT ?, ?";
            return jdbcTemplate.query(sql, new Object[]{"%" + userCondition.getName() + "%", (pageNum - 1) * pageSize, pageSize}, (rs, rowNum) -> {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                return user;
            });
        }

        // 根据条件统计用户数量
        public int countUsersByCondition(User userCondition) {
            String sql = "SELECT COUNT(*) FROM tb_user WHERE name LIKE ?";
            return jdbcTemplate.queryForObject(sql, new Object[]{"%" + userCondition.getName() + "%"}, Integer.class);
        }

        // 为用户分配角色
        public void assignRolesToUser(String userId, List<Integer> roleIds) {
            String sql = "INSERT INTO tb_user_role (user_id, role_id) VALUES (?, ?)";
            for (Integer roleId : roleIds) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }

    }
